1. Create a new Visual Studio project.
2. From the Project menu, select Add New Item.
3. In the Add New Item dialog that appears, select ActiveReports 7 Page Report and in the Name field, rename the file as DynamicQueries.
4. Click the Add button to open a new fixed page report in the designer.

See Adding an ActiveReport to a Project for information on adding different report layouts.

1. In the Report Explorer, right-click the Data Sources node and select the Add Data Source option or select Data Source from the Add button.
   
2. In the Report Data Source Dialog that appears, select the General page and in the Name field, enter a name like ReportData.
3. On this page, create a connection to the Reels database. See Connect to a Data Source for information on connecting to a data source.

1. In the Report Explorer, right-click the data source node and select the Add Data Set option or select Data Set from the Add button.
2. In the DataSet Dialog that appears, select the General page and enter the name of the dataset as Products. This name appears as a child node to the data source icon in the Report Explorer.
3. On the Query page of this dialog, in the Query field enter the following SQL query.

   SQL Query	Copy Code
   SELECT Movie.Title, Product.InStock, Product.StorePrice FROM MediaType INNER JOIN (Movie INNER JOIN (Product INNER JOIN MovieProduct ON Product.ProductID = MovieProduct.ProductID) ON Movie.MovieID = MovieProduct.MovieID) ON MediaType.MediaID = MovieProduct.MediaType WHERE (((MediaType.MediaID)=1))ORDER BY Movie.Title

4. Click the Validate DataSet icon at the top right hand corner above the Query box to validate the query.
   
   
5. Click OK to close the dialog. Your data set and queried fields appear as nodes in the Report Explorer.

1. From the toolbox, drag a Table data region onto the report design surface and set the following properties in the Properties Window:

   Property Name	Property Value
   Location	0in, 0.5in
   Size	5.5in, 0.75in
   FixedSize (only for FPL reports)	6.5in, 7in

2. In the Report Explorer from the Products dataset, drag the following fields onto the detail row of the table.

   Data Field	Column Name
   Title	TableColumn1
   InStock	TableColumn2
   StorePrice	TableColumn3

   Note: This automatically places an expression in the detail row and simultaneously places a static label in the header row of the same column.

3. Select the header row, click the table handle to the left of the row and in the Properties Window, set the following properties:

   Property Name	Property Value
   FontWeight	Bold
   BackgroundColor	DarkSeaGreen
   RepeatOnNewPage	True

4. Select the StorePrice field in the detail row and in the Properties Window, set its Format property to Currency.

5. Click the column handle at the top of each column in turn to select it, and in the Properties Window, set the Width property as indicated in the table.

   Column	Width
   First	4.5in
   Second	1in
   Third	1in

1. In the Report Explorer, right-click the data source node and select the Add Data Set option or select Data Set from the Add button. 
2. In the DataSet Dialog that appears, select the General page and enter the name of the dataset as MediaType.
3. On the Query page, paste the following SQL command into the Query text box:
   

   SQL Query	Copy Code
   SELECT 0 AS MediaID, "All" AS Description FROM MediaType UNION SELECT MediaID, Description FROM MediaType ORDER BY Description

4. Click the Validate icon to validate the query and to populate the Fields list.
    
5. Click OK to close the dialog. Your data set and queried fields appear as nodes in the Report Explorer.

1. In the Report Explorer, select the Parameters node.
2. Right-click the node and select Add Parameter to open the Report - Parameters dialog.
3. In the dialog box that appears, click the Add(+) button to add a new parameter in the list.
4. Set properties in the following fields below the parameters list.
   

   In the General tab:

   • Name: MediaType
   • DataType: String
   • Text for prompting users for a value: Select a media type

   In the Available Values tab select From query:

   • DataSet: MediaType
   • Value: MediaID
   • Label: Description
5. Click OK to close the dialog and add the parameter to the collection. This parameter appears under the Parameters node in the Report Explorer.

1. In the Report Explorer, right-click the Products dataset and select Edit.
2. In the DataSet dialog that appears, select the Query page.
3. In the Query field, change the query to the following expression:

   Query

   Copy Code

   ="SELECT Movie.Title, Product.InStock, Product.StorePrice, MediaType.Description FROM MediaType INNER JOIN (Movie INNER JOIN (Product INNER JOIN MovieProduct ON Product.ProductID = MovieProduct.ProductID) ON Movie.MovieID = MovieProduct.MovieID) ON MediaType.MediaID = MovieProduct.MediaType" & IIf(Parameters!MediaType.Value = 0, ""," WHERE (MediaType = " & Parameters!MediaType.Value & ")") & " ORDER BY Movie.Title"

4. Click OK to close the dialog.

1. From the toolbox, drag and drop a Textbox control onto the report design surface. In CPL reports, you can place the Textbox control in the PageHeader.
2. Select the Textbox and set the following properties in the Properties window.

   Property Name	Property Value
   TextAlign	Center
   FontSize	14pt
   Location	0in, 0in
   Size	6.5in, 0.25in
   Value	=Parameters!MediaType.Label & " Movies in Stock"

   Note: Using Label instead of Value in the expression displays a more readily understandable Description field instead of the MediaID field we used for the parameter's value.

• Go to the preview tab and select a parameter in the Parameters pane to view the report at design time.

OR

• Open the report in the Viewer and select a parameter in the Parameters pane to view the report. See Using the Viewer for further information.

Caution: For an FPL report, you must set the name of the dataset you are using in the report in the Dataset name of FixedPage Dialog before you preview the report. For example, Products in this walkthrough.